/*
 * Manage an accommodation session
 */
package com.spartancoder.accommodation.models;

import com.spartancoder.accommodation.beans.AccommodationSessionBean;
import com.spartancoder.accommodation.db.Database;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Logger;

/**
 *
 * @author andrei
 */
public class AccommodationSession {

	private static Logger log = Logger.getLogger(AccommodationSession.class.getName());
	private static Database db = Database.getInstance();

	public static void create(AccommodationSessionBean as) {
		try {
			Connection conn = db.getConnection();
			String query = "insert into `accommodation_sessions`" +
					"(`name`, `start_date`, `end_date`)" +
					"values(?,?,?)";
			PreparedStatement st = conn.prepareStatement(query);
			st.setString(1, as.getName());
			st.setTimestamp(2, as.getStartDate());
			st.setTimestamp(3, as.getEndDate());

			st.execute();

			st.close();
			conn.close();
		} catch (SQLException ex) {
			log.severe(ex.toString());
			throw new InternalError(ex.toString());
		}
	}

	public static void createActive(String name) {
		stopActive();
		try {
			Connection conn = db.getConnection();
			String query = "insert into `accommodation_sessions`" +
					"(`name`, `start_date`, `end_date`)" +
					"values(?,now(),(now() + interval 1 year))";
			PreparedStatement st = conn.prepareStatement(query);
			st.setString(1, name);
			st.execute();
		} catch (SQLException ex) {
			log.severe(ex.toString());
			throw new InternalError(ex.toString());
		}
	}

	public static void update(AccommodationSessionBean session) {
		try {
			Connection conn = db.getConnection();
			String query = "update into `accommodation_sessions` " +
					"set `name`=?, `start_date`=?, `end_date`=? " +
					"where `session_id`=?";
			PreparedStatement st = conn.prepareStatement(query);

			st.setString(1, session.getName());
			st.setTimestamp(2, session.getStartDate());
			st.setTimestamp(3, session.getEndDate());
			st.setInt(4, session.getSessionId());

			st.execute();

			st.close();
			conn.close();
		} catch (SQLException ex) {
			log.severe(ex.toString());
			throw new InternalError(ex.toString());
		}
	}

	public static AccommodationSessionBean fetch(int sessionId) {
		try {
			Connection conn = db.getConnection();
			String query = "select * from `accommodation_sessions` " +
					"where `session_id`=? limit 1";
			PreparedStatement st = conn.prepareStatement(query);
			st.setInt(1, sessionId);

			ResultSet rs = st.executeQuery();
			if (rs.next()) {
				AccommodationSessionBean ret = new AccommodationSessionBean();
				ret.fill(rs);
				return ret;
			}

			return null;
		} catch (SQLException ex) {
			log.severe(ex.toString());
			throw new InternalError(ex.toString());
		}
	}

	public static AccommodationSessionBean fetchActive() {
		try {
			Connection conn = db.getConnection();
			String query = "select * from `accommodation_sessions` " +
					"where ( `end_date` is null or `end_date` > now() )";
			PreparedStatement st = conn.prepareStatement(query);

			ResultSet rs = st.executeQuery();
			if (rs.next()) {
				AccommodationSessionBean ret = new AccommodationSessionBean();
				ret.fill(rs);
				return ret;
			}

			return null;
		} catch (SQLException ex) {
			log.severe(ex.toString());
			throw new InternalError(ex.toString());
		}
	}

	
	public static AccommodationSessionBean fetchLast() {
		try {
			Connection conn = db.getConnection();
			String query = "select * from `accommodation_sessions` " +
					"order by `start_date` desc limit 1";
			PreparedStatement st = conn.prepareStatement(query);

			ResultSet rs = st.executeQuery();
			if (rs.next()) {
				AccommodationSessionBean ret = new AccommodationSessionBean();
				ret.fill(rs);
				return ret;
			}

			return null;
		} catch (SQLException ex) {
			log.severe(ex.toString());
			throw new InternalError(ex.toString());
		}
	}

	public static void stopActive() {
		try {
			Connection conn = db.getConnection();
			String query = "update `accommodation_sessions` set `end_date`=now() " +
					"where ( `end_date` is null or `end_date` > now() )";
			PreparedStatement st = conn.prepareStatement(query);
			st.executeUpdate();
		} catch (SQLException ex) {
			log.severe(ex.toString());
			throw new InternalError(ex.toString());
		}
	}
}
